use Fieldbook
go

/* create table MachineCategory */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MachineCategory')
BEGIN
 CREATE TABLE dbo.MachineCategory
	(
	CategoryID bigint IDENTITY(1,1) NOT NULL,
	Description nvarchar(50) NOT NULL,
	HouseHoldID bigint NULL,
	)  ON [PRIMARY]
END 
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_MACHINECATEGORY')
BEGIN
ALTER TABLE dbo.MachineCategory ADD CONSTRAINT
	PK_MachineCategory PRIMARY KEY CLUSTERED 
	(
	CategoryID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_MACHINECATEGORY_APPUSER')
BEGIN
ALTER TABLE dbo.MachineCategory ADD CONSTRAINT
	FK_MachineCategory_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END 
GO

/* Create table machine */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Machine')
BEGIN
CREATE TABLE dbo.Machine
	(
	MachineID bigint IDENTITY(1,1) NOT NULL,
	CategoryID bigint NOT NULL,
	Registration nvarchar(20) NOT NULL,
	HouseHoldID bigint NULL
	)  ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_MACHINE')
BEGIN
ALTER TABLE dbo.Machine ADD CONSTRAINT
	PK_Machine PRIMARY KEY CLUSTERED 
	(
	MachineID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_Machine_MachineCategory')
BEGIN
ALTER TABLE dbo.Machine ADD CONSTRAINT
	FK_Machine_MachineCategory FOREIGN KEY
	(
	CategoryID
	) REFERENCES dbo.MachineCategory
	(
	CategoryID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END 
GO

IF NOT EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_Machine_AppUser')
BEGIN
ALTER TABLE dbo.Machine ADD CONSTRAINT
	FK_Machine_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

/* extending table operation wiht Operaton Type */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Operation' AND COLUMN_NAME = 'OperationType')
BEGIN
ALTER TABLE dbo.Operation ADD OperationType TINYINT 
UPDATE dbo.Operation SET OperationType = 0
ALTER TABLE dbo.Operation ALTER COLUMN OperationType TINYINT NOT NULL
END
GO

/* every machine must belogn to someones household */
ALTER TABLE dbo.Machine ALTER COLUMN HouseHoldID bigint NOT NULL 
GO

/* optional field for machine */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Machine' AND COLUMN_NAME = 'Description')
BEGIN
ALTER TABLE dbo.Machine ADD Description nvarchar(256) NULL 
END
GO

/* optional field for machine */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Machine' AND COLUMN_NAME = 'Power')
BEGIN
ALTER TABLE dbo.Machine ADD Power decimal(19,2) NULL 
END
GO

/* optional field for machine */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Machine' AND COLUMN_NAME = 'PowerMU')
BEGIN
ALTER TABLE dbo.Machine ADD PowerMU int NULL 
END
GO

IF NOT EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_Machine_MeasureUnit')
BEGIN
ALTER TABLE dbo.Machine ADD CONSTRAINT
	FK_Machine_MeasureUnit FOREIGN KEY
	(
	PowerMU
	) REFERENCES dbo.MeasureUnit
	(
	MeasureUnitID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO


/* optional field for machine */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Machine' AND COLUMN_NAME = 'Volumen')
BEGIN
ALTER TABLE dbo.Machine ADD Volumen decimal(19, 2) NULL 
END
GO

/* optional field for machine */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Machine' AND COLUMN_NAME = 'VolumenMU')
BEGIN
ALTER TABLE dbo.Machine ADD VolumenMU int NULL 
END
GO

IF NOT EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_Machine_MeasureUnit1')
BEGIN
ALTER TABLE dbo.Machine ADD CONSTRAINT
	FK_Machine_MeasureUnit1 FOREIGN KEY
	(
	VolumenMU
	) REFERENCES dbo.MeasureUnit
	(
	MeasureUnitID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

/* optional field for machine */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Machine' AND COLUMN_NAME = 'ProductionYear')
BEGIN
ALTER TABLE dbo.Machine ADD ProductionYear int NULL 
END
GO

/* rename table column */
if exists (select 1 from information_schema.columns where table_name = 'FieldWork' and column_name = 'ExecuionDate')
begin
EXECUTE sp_rename N'dbo.FieldWork.ExecuionDate', N'Tmp_ExecutionDate', 'COLUMN' 
EXECUTE sp_rename N'dbo.FieldWork.Tmp_ExecutionDate', N'ExecutionDate', 'COLUMN' 
end
go

/* create table FieldMap */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldMap')
BEGIN 
CREATE TABLE dbo.FieldMap
	(
	FieldMapID int IDENTITY(1,1) NOT NULL,
	PostalCode nvarchar(10) NOT NULL,
	Description nvarchar(50) NOT NULL,
    MapFileName UNIQUEIDENTIFIER NOT NULL
	)  ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_FieldMap')
BEGIN
ALTER TABLE dbo.FieldMap ADD CONSTRAINT
	PK_FieldMap PRIMARY KEY CLUSTERED 
	(
	FieldMapID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

/* create table fieldworkMachine */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldWorkMachine')
CREATE TABLE dbo.FieldWorkMachine
	(
	FieldWorkMachineID bigint IDENTITY(1,1) NOT NULL,
	FieldWorkID bigint NOT NULL,
	MachineID bigint NOT NULL,
	MeasureUnitID int NOT NULL,  
	Quota decimal(19, 2) NOT NULL
	)  ON [PRIMARY]
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_FieldWorkMachine')
BEGIN
ALTER TABLE dbo.FieldWorkMachine ADD CONSTRAINT
	PK_FieldWorkMachine PRIMARY KEY CLUSTERED 
	(
	FieldWorkMachineID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_FieldWorkMachine_FieldWork')
BEGIN
ALTER TABLE dbo.FieldWorkMachine ADD CONSTRAINT
	FK_FieldWorkMachine_FieldWork FOREIGN KEY
	(
	FieldWorkID
	) REFERENCES dbo.FieldWork
	(
	FieldWorkID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_FieldWorkField_Machine')
BEGIN
ALTER TABLE dbo.FieldWorkMachine ADD CONSTRAINT
	FK_FieldWorkField_Machine FOREIGN KEY
	(
	MachineID
	) REFERENCES dbo.Machine
	(
	MachineID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_FieldWorkField_MeasureUnit')
BEGIN
ALTER TABLE dbo.FieldWorkMachine ADD CONSTRAINT
	FK_FieldWorkField_MeasureUnit FOREIGN KEY
	(
	MeasureUnitID
	) REFERENCES dbo.MeasureUnit
	(
	MeasureUnitID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

/* create table plantSpecie */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PlantSpecie')
CREATE TABLE dbo.PlantSpecie
	(
	PlantSpecieID bigint IDENTITY(1,1) NOT NULL,
	CategoryID int NOT NULL,
	Description nvarchar(50) NOT NULL,
	)  ON [PRIMARY]
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_PlantSpecie')
BEGIN
ALTER TABLE dbo.PlantSpecie ADD CONSTRAINT
	PK_PlantSpecie PRIMARY KEY CLUSTERED 
	(
	PlantSpecieID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

/* create table FieldPlantSpecie */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldPlantSpecie')
BEGIN
CREATE TABLE dbo.FieldPlantSpecie
	(
	FieldPlantSpecieID bigint IDENTITY(1,1) NOT NULL,
	FieldID bigint NOT NULL,
	PlantSpecieID bigint NOT NULL,
	Sort nvarchar(50) NOT NULL,
	SpecieStartDate datetime NOT NULL
	)  ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_FieldPlantSpecie')
BEGIN
ALTER TABLE dbo.FieldPlantSpecie ADD CONSTRAINT
	PK_FieldPlantSpecie PRIMARY KEY CLUSTERED 
	(
	FieldPlantSpecieID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_FieldPlantSpecie_Field')
BEGIN
ALTER TABLE dbo.FieldPlantSpecie ADD CONSTRAINT
	FK_FieldPlantSpecie_Field FOREIGN KEY
	(
	FieldID
	) REFERENCES dbo.Field
	(
	FieldID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_FieldPlantSpecie_PlantSpecie')
BEGIN
ALTER TABLE dbo.FieldPlantSpecie ADD CONSTRAINT
	FK_FieldPlantSpecie_PlantSpecie FOREIGN KEY
	(
	PlantSpecieID
	) REFERENCES dbo.PlantSpecie
	(
	PlantSpecieID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

/* adding filed state, which can be changed by the filed work */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Field' AND COLUMN_NAME = 'FieldStateID')
BEGIN
ALTER TABLE dbo.Field ADD FieldStateID tinyint NULL 
END
GO

/* adding filed state, which can be changed by the filed work */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Field' AND COLUMN_NAME = 'FieldMapID')
BEGIN
ALTER TABLE dbo.Field ADD FieldMapID int NULL 
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_Field_FieldMapID')
BEGIN
ALTER TABLE dbo.Field ADD CONSTRAINT
	FK_Field_FieldMapID FOREIGN KEY
	(
	FieldMapID
	) REFERENCES dbo.FieldMap
	(
	FieldMapID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

/* adding filed state, which can be changed by the filed work */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Field' AND COLUMN_NAME = 'FieldMapX')
BEGIN
    ALTER TABLE dbo.Field ADD FieldMapX smallint NULL 
END
GO

/* adding filed state, which can be changed by the filed work */
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Field' AND COLUMN_NAME = 'FieldMapY')
BEGIN
    ALTER TABLE dbo.Field ADD FieldMapY smallint NULL 
END
GO

/* create table UserMaps */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserMap')
BEGIN
CREATE TABLE dbo.UserMap
	(
	UserID bigint NOT NULL,
	FieldMapID int NOT NULL,
	)  ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'PK_UserMap')
BEGIN
ALTER TABLE dbo.UserMap ADD CONSTRAINT
	PK_UserMap PRIMARY KEY CLUSTERED 
	(
	UserID ASC, FieldMapID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_UserMap_AppUser')
BEGIN
ALTER TABLE dbo.UserMap ADD CONSTRAINT
	FK_UserMap_AppUser FOREIGN KEY
	(
	UserID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

IF NOT EXISTS (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME = 'FK_UserMap_FieldMap')
BEGIN
ALTER TABLE dbo.UserMap ADD CONSTRAINT
	FK_UserMap_FieldMap FOREIGN KEY
	(
	FieldMapID
	) REFERENCES dbo.FieldMap
	(
	FieldMapID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO